package service

import (
	"context"
	"fmt"
	"strings"

	"ddl_checker/internal/model"
	"ddl_checker/pkg/database"
)

// SchemaService DDL模式服务
type SchemaService struct {
	db *database.DB
}

// NewSchemaService 创建模式服务实例
func NewSchemaService(db *database.DB) *SchemaService {
	return &SchemaService{
		db: db,
	}
}

// GetDatabaseSchema 获取数据库完整模式信息
func (s *SchemaService) GetDatabaseSchema(ctx context.Context, schemaName string) (*model.DatabaseSchema, error) {
	if schemaName == "" {
		schemaName = "public"
	}

	// 并发查询各种信息
	tablesCh := make(chan []model.TableInfo, 1)
	columnsCh := make(chan []model.ColumnInfo, 1)
	indexesCh := make(chan []model.IndexInfo, 1)
	constraintsCh := make(chan []model.ConstraintInfo, 1)

	errCh := make(chan error, 4)

	// 查询表信息
	go func() {
		tables, err := s.GetTables(ctx, schemaName)
		if err != nil {
			errCh <- fmt.Errorf("获取表信息失败: %w", err)
			return
		}
		tablesCh <- tables
	}()

	// 查询列信息
	go func() {
		columns, err := s.GetColumns(ctx, schemaName)
		if err != nil {
			errCh <- fmt.Errorf("获取列信息失败: %w", err)
			return
		}
		columnsCh <- columns
	}()

	// 查询索引信息
	go func() {
		indexes, err := s.GetIndexes(ctx, schemaName)
		if err != nil {
			errCh <- fmt.Errorf("获取索引信息失败: %w", err)
			return
		}
		indexesCh <- indexes
	}()

	// 查询约束信息
	go func() {
		constraints, err := s.GetConstraints(ctx, schemaName)
		if err != nil {
			errCh <- fmt.Errorf("获取约束信息失败: %w", err)
			return
		}
		constraintsCh <- constraints
	}()

	// 等待所有结果
	var tables []model.TableInfo
	var columns []model.ColumnInfo
	var indexes []model.IndexInfo
	var constraints []model.ConstraintInfo

	for i := 0; i < 4; i++ {
		select {
		case err := <-errCh:
			return nil, err
		case tables = <-tablesCh:
		case columns = <-columnsCh:
		case indexes = <-indexesCh:
		case constraints = <-constraintsCh:
		}
	}

	return &model.DatabaseSchema{
		Tables:      tables,
		Columns:     columns,
		Indexes:     indexes,
		Constraints: constraints,
	}, nil
}

// GetTables 获取表信息
func (s *SchemaService) GetTables(ctx context.Context, schemaName string) ([]model.TableInfo, error) {
	// 检查数据库连接是否存在
	if s.db == nil {
		return nil, fmt.Errorf("数据库连接未建立，无法获取表信息")
	}

	query := `
		SELECT
			table_name,
			table_schema,
			table_type,
			COALESCE(obj_description(c.oid), '') as comment
		FROM information_schema.tables t
		LEFT JOIN pg_class c ON c.relname = t.table_name
		LEFT JOIN pg_namespace n ON n.oid = c.relnamespace AND n.nspname = t.table_schema
		WHERE table_schema = $1
		ORDER BY table_name
	`

	rows, err := s.db.Query(ctx, query, schemaName)
	if err != nil {
		return nil, fmt.Errorf("查询表信息失败: %w", err)
	}
	defer rows.Close()

	var tables []model.TableInfo
	for rows.Next() {
		var table model.TableInfo
		err := rows.Scan(
			&table.TableName,
			&table.SchemaName,
			&table.TableType,
			&table.Comment,
		)
		if err != nil {
			return nil, fmt.Errorf("扫描表信息失败: %w", err)
		}
		tables = append(tables, table)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("读取表信息失败: %w", err)
	}

	return tables, nil
}

// GetColumns 获取列信息
func (s *SchemaService) GetColumns(ctx context.Context, schemaName string) ([]model.ColumnInfo, error) {
	// 检查数据库连接是否存在
	if s.db == nil {
		return nil, fmt.Errorf("数据库连接未建立，无法获取列信息")
	}

	query := `
		SELECT 
			c.table_name,
			c.column_name,
			c.data_type,
			c.is_nullable,
			COALESCE(c.column_default, '') as column_default,
			c.character_maximum_length,
			c.numeric_precision,
			c.numeric_scale,
			c.ordinal_position,
			COALESCE(col_description(pgc.oid, c.ordinal_position), '') as comment
		FROM information_schema.columns c
		LEFT JOIN pg_class pgc ON pgc.relname = c.table_name
		LEFT JOIN pg_namespace pgn ON pgn.oid = pgc.relnamespace AND pgn.nspname = c.table_schema
		WHERE c.table_schema = $1
		ORDER BY c.table_name, c.ordinal_position
	`

	rows, err := s.db.Query(ctx, query, schemaName)
	if err != nil {
		return nil, fmt.Errorf("查询列信息失败: %w", err)
	}
	defer rows.Close()

	var columns []model.ColumnInfo
	for rows.Next() {
		var column model.ColumnInfo
		err := rows.Scan(
			&column.TableName,
			&column.ColumnName,
			&column.DataType,
			&column.IsNullable,
			&column.ColumnDefault,
			&column.CharacterMaximumLength,
			&column.NumericPrecision,
			&column.NumericScale,
			&column.OrdinalPosition,
			&column.Comment,
		)
		if err != nil {
			return nil, fmt.Errorf("扫描列信息失败: %w", err)
		}
		columns = append(columns, column)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("读取列信息失败: %w", err)
	}

	return columns, nil
}

// GetIndexes 获取索引信息
func (s *SchemaService) GetIndexes(ctx context.Context, schemaName string) ([]model.IndexInfo, error) {
	// 检查数据库连接是否存在
	if s.db == nil {
		return nil, fmt.Errorf("数据库连接未建立，无法获取索引信息")
	}

	query := `
		SELECT 
			t.relname as table_name,
			i.relname as index_name,
			a.attname as column_name,
			idx.indisunique as is_unique,
			idx.indisprimary as is_primary,
			am.amname as index_type
		FROM pg_class t
		JOIN pg_namespace n ON n.oid = t.relnamespace
		JOIN pg_index idx ON idx.indrelid = t.oid
		JOIN pg_class i ON i.oid = idx.indexrelid
		JOIN pg_am am ON am.oid = i.relam
		JOIN pg_attribute a ON a.attrelid = t.oid 
			AND a.attnum = ANY(idx.indkey)
		WHERE n.nspname = $1
			AND t.relkind = 'r'
		ORDER BY t.relname, i.relname, a.attnum
	`

	rows, err := s.db.Query(ctx, query, schemaName)
	if err != nil {
		return nil, fmt.Errorf("查询索引信息失败: %w", err)
	}
	defer rows.Close()

	var indexes []model.IndexInfo
	for rows.Next() {
		var index model.IndexInfo
		err := rows.Scan(
			&index.TableName,
			&index.IndexName,
			&index.ColumnName,
			&index.IsUnique,
			&index.IsPrimary,
			&index.IndexType,
		)
		if err != nil {
			return nil, fmt.Errorf("扫描索引信息失败: %w", err)
		}
		indexes = append(indexes, index)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("读取索引信息失败: %w", err)
	}

	return indexes, nil
}

// GetConstraints 获取约束信息
func (s *SchemaService) GetConstraints(ctx context.Context, schemaName string) ([]model.ConstraintInfo, error) {
	// 检查数据库连接是否存在
	if s.db == nil {
		return nil, fmt.Errorf("数据库连接未建立，无法获取约束信息")
	}

	query := `
		SELECT 
			tc.table_name,
			tc.constraint_name,
			tc.constraint_type,
			COALESCE(kcu.column_name, '') as column_name,
			COALESCE(ccu.table_name, '') as referenced_table,
			COALESCE(ccu.column_name, '') as referenced_column
		FROM information_schema.table_constraints tc
		LEFT JOIN information_schema.key_column_usage kcu 
			ON tc.constraint_name = kcu.constraint_name 
			AND tc.table_schema = kcu.table_schema
		LEFT JOIN information_schema.constraint_column_usage ccu 
			ON tc.constraint_name = ccu.constraint_name 
			AND tc.table_schema = ccu.table_schema
		WHERE tc.table_schema = $1
		ORDER BY tc.table_name, tc.constraint_name
	`

	rows, err := s.db.Query(ctx, query, schemaName)
	if err != nil {
		return nil, fmt.Errorf("查询约束信息失败: %w", err)
	}
	defer rows.Close()

	var constraints []model.ConstraintInfo
	for rows.Next() {
		var constraint model.ConstraintInfo
		err := rows.Scan(
			&constraint.TableName,
			&constraint.ConstraintName,
			&constraint.ConstraintType,
			&constraint.ColumnName,
			&constraint.ReferencedTable,
			&constraint.ReferencedColumn,
		)
		if err != nil {
			return nil, fmt.Errorf("扫描约束信息失败: %w", err)
		}
		constraints = append(constraints, constraint)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("读取约束信息失败: %w", err)
	}

	return constraints, nil
}

// ValidateTableExists 验证表是否存在
func (s *SchemaService) ValidateTableExists(ctx context.Context, schemaName, tableName string) (bool, error) {
	// 检查数据库连接是否存在
	if s.db == nil {
		return false, fmt.Errorf("数据库连接未建立，无法检查表存在性")
	}

	if schemaName == "" {
		schemaName = "public"
	}

	query := `
		SELECT COUNT(*)
		FROM information_schema.tables
		WHERE table_schema = $1 AND table_name = $2
	`

	var count int
	err := s.db.QueryRow(ctx, query, schemaName, tableName).Scan(&count)
	if err != nil {
		return false, fmt.Errorf("检查表存在性失败: %w", err)
	}

	return count > 0, nil
}

// ValidateColumnExists 验证列是否存在
func (s *SchemaService) ValidateColumnExists(ctx context.Context, schemaName, tableName, columnName string) (bool, error) {
	// 检查数据库连接是否存在
	if s.db == nil {
		return false, fmt.Errorf("数据库连接未建立，无法检查列存在性")
	}

	if schemaName == "" {
		schemaName = "public"
	}

	query := `
		SELECT COUNT(*)
		FROM information_schema.columns
		WHERE table_schema = $1 AND table_name = $2 AND column_name = $3
	`

	var count int
	err := s.db.QueryRow(ctx, query, schemaName, tableName, columnName).Scan(&count)
	if err != nil {
		return false, fmt.Errorf("检查列存在性失败: %w", err)
	}

	return count > 0, nil
}

// GetTableDDL 获取表的DDL语句（GaussDB特定）
func (s *SchemaService) GetTableDDL(ctx context.Context, schemaName, tableName string) (string, error) {
	if schemaName == "" {
		schemaName = "public"
	}

	// 构建CREATE TABLE语句
	var ddlParts []string

	// 获取表基本信息
	columns, err := s.getTableColumns(ctx, schemaName, tableName)
	if err != nil {
		return "", fmt.Errorf("获取表列信息失败: %w", err)
	}

	if len(columns) == 0 {
		return "", fmt.Errorf("表不存在: %s.%s", schemaName, tableName)
	}

	// 构建列定义
	var columnDefs []string
	for _, col := range columns {
		colDef := fmt.Sprintf("    %s %s", col.ColumnName, col.DataType)

		if col.CharacterMaximumLength != nil && *col.CharacterMaximumLength > 0 {
			colDef = fmt.Sprintf("%s(%d)", colDef, *col.CharacterMaximumLength)
		}

		if col.IsNullable == "NO" {
			colDef += " NOT NULL"
		}

		if col.ColumnDefault != "" {
			colDef += fmt.Sprintf(" DEFAULT %s", col.ColumnDefault)
		}

		columnDefs = append(columnDefs, colDef)
	}

	// 构建CREATE TABLE语句
	createTable := fmt.Sprintf("CREATE TABLE %s.%s (\n%s\n);",
		schemaName, tableName, strings.Join(columnDefs, ",\n"))

	ddlParts = append(ddlParts, createTable)

	// 获取索引DDL
	indexes, err := s.getTableIndexes(ctx, schemaName, tableName)
	if err != nil {
		return "", fmt.Errorf("获取表索引失败: %w", err)
	}

	for _, index := range indexes {
		if !index.IsPrimary { // 跳过主键，因为已经包含在表定义中
			indexType := ""
			if index.IsUnique {
				indexType = "UNIQUE "
			}
			indexDDL := fmt.Sprintf("CREATE %sINDEX %s ON %s.%s (%s);",
				indexType, index.IndexName, schemaName, tableName, index.ColumnName)
			ddlParts = append(ddlParts, indexDDL)
		}
	}

	return strings.Join(ddlParts, "\n\n"), nil
}

// getTableColumns 获取指定表的列信息（内部方法）
func (s *SchemaService) getTableColumns(ctx context.Context, schemaName, tableName string) ([]model.ColumnInfo, error) {
	// 检查数据库连接是否存在
	if s.db == nil {
		return nil, fmt.Errorf("数据库连接未建立，无法获取表列信息")
	}

	query := `
		SELECT 
			table_name, column_name, data_type, is_nullable,
			COALESCE(column_default, '') as column_default,
			character_maximum_length, numeric_precision, numeric_scale,
			ordinal_position
		FROM information_schema.columns
		WHERE table_schema = $1 AND table_name = $2
		ORDER BY ordinal_position
	`

	rows, err := s.db.Query(ctx, query, schemaName, tableName)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var columns []model.ColumnInfo
	for rows.Next() {
		var column model.ColumnInfo
		err := rows.Scan(
			&column.TableName,
			&column.ColumnName,
			&column.DataType,
			&column.IsNullable,
			&column.ColumnDefault,
			&column.CharacterMaximumLength,
			&column.NumericPrecision,
			&column.NumericScale,
			&column.OrdinalPosition,
		)
		if err != nil {
			return nil, err
		}
		columns = append(columns, column)
	}

	return columns, rows.Err()
}

// getTableIndexes 获取指定表的索引信息（内部方法）
func (s *SchemaService) getTableIndexes(ctx context.Context, schemaName, tableName string) ([]model.IndexInfo, error) {
	// 检查数据库连接是否存在
	if s.db == nil {
		return nil, fmt.Errorf("数据库连接未建立，无法获取表索引信息")
	}

	query := `
		SELECT 
			t.relname as table_name,
			i.relname as index_name,
			a.attname as column_name,
			idx.indisunique as is_unique,
			idx.indisprimary as is_primary,
			am.amname as index_type
		FROM pg_class t
		JOIN pg_namespace n ON n.oid = t.relnamespace
		JOIN pg_index idx ON idx.indrelid = t.oid
		JOIN pg_class i ON i.oid = idx.indexrelid
		JOIN pg_am am ON am.oid = i.relam
		JOIN pg_attribute a ON a.attrelid = t.oid 
			AND a.attnum = ANY(idx.indkey)
		WHERE n.nspname = $1 AND t.relname = $2
		ORDER BY i.relname, a.attnum
	`

	rows, err := s.db.Query(ctx, query, schemaName, tableName)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var indexes []model.IndexInfo
	for rows.Next() {
		var index model.IndexInfo
		err := rows.Scan(
			&index.TableName,
			&index.IndexName,
			&index.ColumnName,
			&index.IsUnique,
			&index.IsPrimary,
			&index.IndexType,
		)
		if err != nil {
			return nil, err
		}
		indexes = append(indexes, index)
	}

	return indexes, rows.Err()
}
